﻿
-- ================================================
-- Remarks: Not part of standard Stored Proc
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Ivy_SiteAdministration_UserRoleAllocation_GetAllByUserKeyOrganizationKey')
	BEGIN
		DROP  Procedure  Ivy_SiteAdministration_UserRoleAllocation_GetAllByUserKeyOrganizationKey
	END

GO

CREATE PROCEDURE [Ivy_SiteAdministration_UserRoleAllocation_GetAllByUserKeyOrganizationKey] 
-- =============================================
-- Author:		<Ram pravesh kr>
-- Create date: <25/06/2016>
-- Description:	<Ivy_UserRoleAllocation_GetAllByUserKeyOrganizationKey>
-- =============================================
-- Add the parameters for the stored procedure here
	(
				@UserKey int,
				@UserOrganizationKey int
	)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT OFF;

	SELECT DISTINCT
				[UserOrganizationRole].[UserKey], 
				[User].[UserLoginId], 
				[Person].[PersonLastName], 
				[Person].[PersonFirstName], 
				[Person].[PersonPrimaryEmailID], 
				[UserOrganizationRole].[UserOrganizationKey],
				[UserOrganizationRole].[UserOrganizationCode],
				[UserOrganizationRole].[UserOrganizationName]
	FROM        
				[Person] RIGHT OUTER JOIN [User] 
	ON			[Person].[PersonKey] = [User].[UserPersonKey] LEFT OUTER JOIN [UserOrganizationRole] 
	ON			[User].[UserPersonKey] = [UserOrganizationRole].[UserKey]
	WHERE     
		(
				[UserOrganizationRole].[UserKey] = @UserKey)AND 
		(
				[UserOrganizationRole].[UserOrganizationKey] = @UserOrganizationKey
		)
END
GO
